﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using JKF.DataModule.IDAL;
using JKF.Substructure;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data.SqlClient;
using JKF.Entities.Model;

namespace JKF.DataModule.SqlServerDAL
{
    /// <summary>
	/// 数据访问类dalSYS_USER。
	/// </summary>
    public partial class dalSYS_USER : IdalSYS_USER
    {
        // 定义要获取哪些字段USER_KEY, ACCOUNT, PWD, REAL_NAME, GENDER, PHONE, MOBILE, ROLE_KEY, AVAILABLE, SHOP_CODE, DATE_CREATE, DATE_UPDATE 
        private const string FieldShow = "USER_KEY, ACCOUNT, PWD, REAL_NAME, GENDER, PHONE, MOBILE, [dbo].[getRoleNameByKey](ROLE_KEY) AS ROLE_KEY, AVAILABLE, [dbo].[getShopNameByKey](SHOP_CODE) AS SHOP_CODE, DATE_CREATE, DATE_UPDATE";
        private const string FieldOrder = "ACCOUNT,REAL_NAME";
        private const string FieldKey = "USER_KEY";
        /// <summary>
        /// 获取用户列表
        /// </summary>
        /// <param name="ACCOUNT">账号</param>
        /// <param name="REAL_NAME">姓名</param>
        /// <param name="ROLE_KEY">角色ID</param>
        /// <param name="SHOP_CODE">店面编码</param>
        /// <param name="AVAILABLE">是否有效</param>
        /// <param name="pageCurrent">当前页码</param>
        /// <param name="pageSize">一页显示多少</param>
        /// <param name="pageCount">总页数</param>
        /// <param name="rowCount">总记录数</param>
        /// <returns>返回DataTable数据</returns>
        public static DataTable GetUsersDAL(SYS_USERinfo userInfo, string ACCOUNT, string REAL_NAME, string ROLE_KEY, string SHOP_CODE, string AVAILABLE, int pageCurrent, int pageSize, ref int pageCount, ref int rowCount)
        {
            // 根据当前用户ID去加载对应所属本店管理的账户数据
            var sql = new StringBuilder();
            SYS_ROLEinfo roleInfo = new dalSYS_ROLE().GetModel(userInfo.ROLE_KEY);
            if (SysConfig.HeadShop.Contains(roleInfo.ROLE_NAME))
            {//AVAILABLE='1' and 
                sql.AppendFormat("  USER_KEY !='{0}'",userInfo.USER_KEY.ToString()); //加载有效账户
            }
            else if (SysConfig.AreaShop.Contains(roleInfo.ROLE_NAME))
            {

                sql.AppendFormat(@" USER_KEY !='{0}' AND SHOP_CODE IN( SELECT B.SHOP_CODE FROM SYS_USER AS A INNER JOIN SYS_USER_SHOP AS B ON A.USER_KEY=B.USER_KEY WHERE B.USER_KEY='{0}') ", userInfo.USER_KEY.ToString());
            } 
            else
            {
                sql.AppendFormat(@"USER_KEY !='{0}' AND SHOP_CODE='{1}' AND (admin_areas is null or admin_areas='') AND role_key not in(select role_key from sys_role where role_name='{2}')", userInfo.USER_KEY.ToString(), userInfo.SHOP_CODE,SysConfig.HeadShop);
            }
            
            // 下面组装查询条件
            if (!string.IsNullOrEmpty(ACCOUNT))
            {
                sql.AppendFormat(" AND ACCOUNT  LIKE '%{0}%'", ACCOUNT);
            }
            if (!string.IsNullOrEmpty(REAL_NAME))
            {
                sql.AppendFormat(" AND REAL_NAME  LIKE '%{0}%'", REAL_NAME);
            }
            if (!string.IsNullOrEmpty(ROLE_KEY))
            {
                sql.AppendFormat(" AND ROLE_KEY = '{0}'", ROLE_KEY);
            }
            if (!string.IsNullOrEmpty(SHOP_CODE))
            {
                sql.AppendFormat(" AND SHOP_CODE  = '{0}'", SHOP_CODE);
            }
            if (!string.IsNullOrEmpty(AVAILABLE))
            {
                sql.AppendFormat(" AND AVAILABLE  = '{0}'", AVAILABLE);
            }
            // 下面调用此方法去查询数据库
            return DataPage.GetPageInfo("dbo.SYS_USER", FieldKey, pageCurrent, pageSize, FieldShow, FieldOrder, sql.ToString(), ref pageCount, ref rowCount).Tables[0];

        }


        /// <summary>
        /// 判断是否存在此名称,true表示存在
        /// </summary>
        /// <param name="shop_name">店面名称</param>
        /// <returns></returns>
        public static bool ExistsUserAccountDAL(string user_account)
        {
            object obj = DB.HR.ExecuteScalar(CommandType.Text, "select count(1) from dbo.SYS_USER where ACCOUNT='" + user_account + "'");
            return Convert.ToInt32(obj) > 0;
        }

        /// <summary>
        /// 采用事务的方式向sys_user和sys_user_shop表中插入和更新数据
        /// </summary>
        public static bool TransactionInsertORUpdateUser_ShopDAL(string sql)
        {
            //采用事务提交
            using (TransactionHR tHR = DB.BeginTransactionHR())
            {
                int result = tHR.ExecuteNonQuery(sql);
                if (result > 0)
                {
                    tHR.Commit();
                    return true;
                }
                else
                {
                    tHR.Rollback();
                    return false;
                }
            }
        }
        /// <summary>
        /// 用于登陆
        /// </summary>
        /// <param name="username"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public static DataSet SysUserInfo(string username, string pwd)
        {
            Database db = DB.HR;
            string sql = "select * from sys_user where AVAILABLE = 1 AND ACCOUNT =@ACCOUNT AND PWD =@PWD ";
            DbCommand dbCommand = db.GetSqlStringCommand(sql.ToString());
            db.AddInParameter(dbCommand, "ACCOUNT", DbType.String, username);
            db.AddInParameter(dbCommand, "PWD", DbType.String, pwd);
            var ds = db.ExecuteDataSet(dbCommand);
            //var ds = DB.HR.ExecuteDataSetSql(sql, (username.ToLower() + pwd).ToMD5(), username.ToLower());
            return ds;
        }
        /// <summary>
        /// 删除账户,采用存储过程。先留着接口，后续再完善
        /// </summary>
        /// <param name="user_key"></param>
        public static string SysUserDeleteDAL(string user_key)
        {
            int result=-1; 
            Database db = DB.HR;
            DbCommand dbCommand = db.GetStoredProcCommand("[dbo].[proc_UserDelete]");
            db.AddInParameter(dbCommand, "@user_key", DbType.Guid, new Guid(user_key));
            //db.AddOutParameter(dbCommand, "@result", DbType.Int32, result);
            return  db.ExecuteScalar(dbCommand).ToString();
            //return dbCommand.Parameters["@result"].Value.ToString();
        }

        /// <summary>
        /// 构建产品分类与产品的树结构数据
        /// </summary> 
        /// <returns>返回DataTable结果集</returns>
        public static DataTable GetShopProvinceTreeDAL()
        {
            Database db = DB.HR;
            DbCommand dbCommand = db.GetStoredProcCommand("[dbo].[proc_ProvinceShop_ToTree]");
            return db.ExecuteDataSet(dbCommand).Tables[0];
        }
    }
}
